package com.clsfd.database;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.HashMap;
import java.util.Properties;
import java.util.logging.Level;


public class DatabaseConnection {
	private Connection connection = null;
    private Statement statement = null;
    private ResultSet results = null;
	private Properties properties=null;
	private String thisOperator="";
	private SystemParams sysparams=null;
	public DatabaseConnection(Properties properties) throws FileNotFoundException, IOException, SQLException, ClassNotFoundException{
		
        this.properties=properties;
        String mysqldatabase= "";
		String mysqlurl = "";
        String username ="";
        String password ="";
        if (properties!=null){
        	mysqldatabase= properties.getProperty("database.name");
    		mysqlurl = "jdbc:mysql://localhost:3306/"+mysqldatabase;
            username = properties.getProperty("database.username");
            password = properties.getProperty("database.password");
        }
        else{
        	 mysqldatabase= "classified";
     		 mysqlurl = "jdbc:mysql://localhost:3306/"+mysqldatabase;
             username = "root";
             password = "abc123";
        }
       
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection(mysqlurl, username, password);
    	statement = connection.createStatement();
    	sysparams=getSystemParameters();
    	thisOperator=sysparams.getThisOperator();
   
        	
	}
	
	public Collection<Alert> getExpiringAlerts(int count) throws SQLException{		
		Collection<Alert> alerts =  new ArrayList<Alert>();
		results = statement.executeQuery("SELECT `alertID`,`activatedtime`,`serviceitem`,`servicetype`,`advertid`,`operator`,`status`,`expdate`,`smseng`,`smssin`,`smstam` FROM `alerts` where `expdate` <CURDATE() and `status`='ACTIVE' and `operator`='"+thisOperator+"'limit "+count);
		
        while (results.next()) {
        	alerts.add(new Alert(this,results.getInt("alertID"), results.getTimestamp("activatedtime"), results.getInt("serviceitem"),results.getString("servicetype"), results.getInt("advertid"), results.getString("operator"), results.getString("status"), results.getDate("expdate"), results.getString("smseng"), results.getString("smssin"), results.getString("smstam")));
        }
        
        return alerts;
			
	    
	}
	
	public Collection<Alert> getActiveAlerts(int count) throws SQLException{
		// Delete records from the AlertStatus table if not exist in Alerts table
		sqlUpdate("delete from alertstatus where not exists (Select alerts.alertid  from `alerts` where alerts.alertid=alertstatus.alertid )");

		// insert the new Alerts to the AlertStatus table if not exist 
		sqlUpdate("insert into alertstatus  SELECT null as id , A.alertid,0 as delivered, CURRENT_TIMESTAMP  FROM `alerts` A left join alertstatus T on A.alertid=T.alertid where T.id is null");
		
		
		Collection<Alert> alerts =  new ArrayList<Alert>();
		String querystring = 	"SELECT A.`alertID`,T.id as localalertid,`serviceitem`,`servicetype`,`smseng`,`smssin`,`smstam`,T.delivered FROM `alertstatus` T inner join alerts A on T.alertid=A.alertid where A.`status` ='ACTIVE' and T.delivered=0 order by T.id limit "+count;
		results = statement.executeQuery(querystring);
		
		while (results.next()) {
			alerts.add(new Alert(this,results.getInt("alertID"),results.getInt("localalertID"),  results.getInt("serviceitem"), results.getString("smseng"), results.getString("smssin"), results.getString("smstam"), results.getBoolean("delivered")));
        }
		
		java.util.Date date = new java.util.Date();
		Calendar cal = Calendar.getInstance();  
		cal.setTime(date);  
		cal.add(Calendar.HOUR, -Integer.parseInt((sysparams.getParameter("Send Old alerts Timer")))); 
		date = cal.getTime();
		String beforexhours = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(date);
		
		if (count>alerts.size()){
			count=count-alerts.size();
			querystring="SELECT A.`alertID`,T.id as localalertid,`serviceitem`,`servicetype`,`smseng`,`smssin`,`smstam`,T.delivered FROM `alertstatus` T inner join alerts A on T.alertid=A.alertid where A.`status` ='ACTIVE' and T.delivered=1 and exists (select subscriptionid from subscriptions where serviceitem=A.serviceitem and lastalertsenttime<'"+beforexhours+"') order by T.id desc limit "+count;
			results = statement.executeQuery(querystring);
			while (results.next()) {
				alerts.add(new Alert(this,results.getInt("alertID"),results.getInt("localalertID"),  results.getInt("serviceitem"), results.getString("smseng"), results.getString("smssin"), results.getString("smstam"), results.getBoolean("delivered")));
	        }
		}
        return alerts;
			
	    
	}
	
	public Collection<Subscription> getActiveSubscriptions(int count) throws SQLException{
	
		Collection<Subscription> subscriptions =  new ArrayList<Subscription>();
		String subselect = "SELECT `subscriptionID`,`activatedtime`,S.msisdn,`servicetype`,`serviceitem`, `status`, `lastalertid`, `expiredate`, `startalertid`,L.language,`lastalertsenttime`,`alertcount`  FROM `subscriptions` S left join language L on S.msisdn=L.msisdn where S.status ='ACTIVE' limit  "+count;
		results = statement.executeQuery(subselect);
		while (results.next()) {
        	subscriptions.add(new Subscription(this,results.getInt("subscriptionID"), results.getTimestamp("activatedtime"), results.getString("msisdn"), results.getString("servicetype"),results.getInt("serviceitem"),results.getString("status"),results.getInt("lastalertid"), results.getDate("expiredate"), results.getInt("startalertid"),results.getString("language"),results.getTimestamp("lastalertsenttime"),results.getInt("alertcount")));
        																																									
        }
        
        return subscriptions;
	
	}
	
	public Collection<Subscription> getExpiringSubscriptions(int count) throws SQLException{
		
		Collection<Subscription> subscriptions =  new ArrayList<Subscription>();
		results = statement.executeQuery("SELECT `subscriptionID`,`activatedtime`,S.msisdn,`servicetype`,`serviceitem`, `status`, `lastalertid`, `expiredate`, `startalertid`,L.language,`lastalertsenttime`,`alertcount`  FROM `subscriptions` S left join language L on S.msisdn=L.msisdn  where S.`expiredate` <CURDATE() and `status`='ACTIVE' limit "+count);
		while (results.next()) {
        	subscriptions.add(new Subscription(this,results.getInt("subscriptionID"), results.getTimestamp("activatedtime"), results.getString("msisdn"), results.getString("servicetype"),results.getInt("serviceitem"),results.getString("status"),results.getInt("lastalertid"), results.getDate("expiredate"),  results.getInt("startalertid"),results.getString("language"),results.getTimestamp("lastalertsenttime"),results.getInt("alertcount")));
        																																									
        }
        
        return subscriptions;
	
	}
	
	public ResultSet sqlSelect(String querystatement) throws SQLException{
		
		return statement.executeQuery(querystatement);

	}
	
	public SystemParams getSystemParameters(){
		return new SystemParams(this);
	}
	
	public Boolean sqlUpdate(String querystatement) throws SQLException{
		
		return statement.execute(querystatement);

	}
	
	public void close() throws SQLException{
		
		if (results!=null)results.close();
		if (statement!=null)statement.close();
		if (connection!=null)connection.close();
		
		
	}

	public Collection<Subscription> getActiveSubscriptions(Alert alert,int count) throws SQLException{
	
		Collection<Subscription> subscriptions =  new ArrayList<Subscription>();
		String sqlstring="SELECT `subscriptionID`,`activatedtime`,S.msisdn,`servicetype`,`serviceitem`, `status`, `lastalertid`, `expiredate` ,`startalertid`,L.language,`lastalertsenttime`,`alertcount`  FROM `subscriptions` S left join language L on S.msisdn=L.msisdn where S.status ='ACTIVE' and S.serviceitem ="+alert.getServiceitem()+" and S.lastalertid <"+alert.getAlertID()+" limit  "+count;
		results = statement.executeQuery(sqlstring);
		while (results.next()) {
	    	subscriptions.add(new Subscription(this,results.getInt("subscriptionID"), results.getTimestamp("activatedtime"), results.getString("msisdn"), results.getString("servicetype"),results.getInt("serviceitem"),results.getString("status"),results.getInt("lastalertid"), results.getDate("expiredate"),  results.getInt("startalertid"),results.getString("language"),results.getTimestamp("lastalertsenttime"),results.getInt("alertcount")));
	    																																									
	    }
			    
		if (count>subscriptions.size()){
			count=count-subscriptions.size();
			sqlstring="SELECT `subscriptionID`,`activatedtime`,S.msisdn,`servicetype`,`serviceitem`, `status`, `lastalertid`, `expiredate`, `startalertid`,L.language,`lastalertsenttime`,`alertcount`  FROM `subscriptions` S left join language L on S.msisdn=L.msisdn where S.status ='ACTIVE' and S.serviceitem ="+alert.getServiceitem()+" and S.startalertid >"+alert.getAlertID()+" and TIMESTAMPDIFF(MINUTE, activatedtime ,now()) >"+120+" limit  "+count;
			results = statement.executeQuery(sqlstring);
			while (results.next()) {
		    	subscriptions.add(new Subscription(this,results.getInt("subscriptionID"), results.getTimestamp("activatedtime"), results.getString("msisdn"), results.getString("servicetype"),results.getInt("serviceitem"),results.getString("status"),results.getInt("lastalertid"), results.getDate("expiredate"),  results.getInt("startalertid"),results.getString("language"),results.getTimestamp("lastalertsenttime"),results.getInt("alertcount")));
		    																																									
		    }
		}
		
	    return subscriptions;
	
	}

	public Subscription getSubscriptionByID(int subscriptionID) throws SQLException{
	
		results = statement.executeQuery("SELECT `subscriptionID`,`activatedtime`,S.msisdn,`servicetype`,`serviceitem`, `status`, `lastalertid`, `expiredate`, `startalertid`,L.language,`lastalertsenttime`,`alertcount`  FROM `subscriptions` S left join language L on S.msisdn=L.msisdn where S.subscriptionID ="+subscriptionID);
		results.next();
	    return new Subscription(this,results.getInt("subscriptionID"), results.getTimestamp("activatedtime"), results.getString("msisdn"), results.getString("servicetype"),results.getInt("serviceitem"),results.getString("status"),results.getInt("lastalertid"), results.getDate("expiredate"), results.getInt("startalertid"),results.getString("language"),results.getTimestamp("lastalertsenttime"),results.getInt("alertcount"));
	        
	
	}

	public Alert getAlertByID(int AlertID) throws SQLException{		
		results = statement.executeQuery("SELECT `alertID`,`activatedtime`,`serviceitem`,`servicetype`,`advertid`,`operator`,`status`,`expdate`,`smseng`,`smssin`,`smstam` FROM `alerts` where `alertID`="+AlertID);
	    results.next();
	    return new Alert(this,results.getInt("alertID"), results.getTimestamp("activatedtime"), results.getInt("serviceitem"),results.getString("servicetype"), results.getInt("advertid"), results.getString("operator"), results.getString("status"), results.getDate("expdate"), results.getString("smseng"), results.getString("smssin"), results.getString("smstam"));
	    	
	    
	}
	
	public void purgeSubscriptions(int daysafterexpiry) throws SQLException{
		sqlUpdate("delete from `subscriptions`  where `expiredate`<DATE_SUB(CURDATE(), INTERVAL "+daysafterexpiry+" DAY)");
	}
	
	public HashMap<Integer, String> getClassifiedItemByParent(int parentid) throws SQLException{		
		HashMap<Integer, String> classific =  new HashMap<Integer, String>();
		results = statement.executeQuery("SELECT `itemid`,`parentid`,`name`,`prompt`,`rank` FROM `classification` where `parentid`="+parentid+" order by rank desc");
	     
	    while (results.next()) {
	    	classific.put(results.getInt("itemid"), results.getString("name"));
	    																																									
	    }
	   
	    return classific;
	}
	
	public void purgeAlerts(int daysafterexpiry) throws SQLException{
		sqlUpdate("delete from `alerts`  where `expdate`<DATE_SUB(CURDATE(), INTERVAL "+daysafterexpiry+" DAY) and `operator`='"+sysparams.getThisOperator()+"'");
		sqlUpdate("delete from `alerts`  where `expdate`<CURDATE() and `operator`<>'"+sysparams.getThisOperator()+"'");
	}
}
